package com.wlhse.util;

import com.wlhse.dao.*;
import com.wlhse.dto.CheckListItemsDto;
import com.wlhse.dto.EmployeeExcelDto;
import com.wlhse.dto.PoiProblemDto;
import com.wlhse.entity.EmployeePojo;
import com.wlhse.util.state_code.CodeDict;
import com.wlhse.util.state_code.NR;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.*;


@Component
public class GetBeanListByExcel {

    @Resource
    private PoiEmploy poiEmploy;

    @Resource
    private PoiProblemDao poiProblemDao;

    @Resource
    private ProblemImportDao problemImportDao;

    @Resource
    private CheckListItemDao checkListItemDao;

    @Resource
    private EmployeeManagementDao employeeManagementDao;

    @Resource
    private CompanyDao companyDao;

    @Resource
    private DeleteCacheUtil deleteCacheUtil;

    public <T> String getBeanList(Workbook wb, String[] cellKey, Class<T> c) throws Exception {
        //创建实体类对象容器
        List<T> beanList = new ArrayList<>();
        HashMap<String, String> valueMap = new HashMap<>();
        Sheet sheet = wb.getSheetAt(0);
        //遍历excel文件
        for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
            Row row = sheet.getRow(j);
            for (int i = 0; i < cellKey.length; i++) {
                Cell cell = row.getCell(i);
                if (cell == null && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                    return NR.getEmptyNote(row.getPhysicalNumberOfCells(), i);
                }
                String cellValue;
                //判断是否是数值格式
                if (row.getCell(i).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    short format = row.getCell(i).getCellStyle().getDataFormat();
                    if (format == 14 || format == 31) {
                        Date date = new Date();
                        if (i == 3) {
                            date = HSSFDateUtil.getJavaDate(row.getCell(3).getNumericCellValue());
                        } else if (i == 4) {
                            date = HSSFDateUtil.getJavaDate(row.getCell(4).getNumericCellValue());
                        }
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = sdf.format(date);
                    } else {
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.getStringCellValue();
                    }
                } else {
                    cellValue = cell.getStringCellValue();
                }
                valueMap.put(cellKey[i], cellValue);
            }
            T t = c.newInstance();
            //使用BeanUtils将封装的属性注入对象
            BeanUtils.populate(t, valueMap);
            //将对象添加至容器
            beanList.add(t);
        }
        if (beanList.size() > 0) {
            beanList.remove(0);
            poiEmploy.poiPoiEmploy((List<EmployeeExcelDto>) beanList);
            return NR.getPoiProblemReturn(CodeDict.SUCCESS, 0);
        } else {
            return NR.getPoiProblemReturn(CodeDict.POI_PROBLEM_UNKNOWN_ERROR, 0);
        }
    }

    @Transactional
    public <T> String getProblemBeanAndPoi(Sheet sheet, String[] cellKey, Class<T> c, int startIdx, int endIdx) throws IllegalAccessException, InstantiationException, InvocationTargetException {
        HashMap<String, String> valueMap = new HashMap<>();
        List<T> beanList = new ArrayList<>();

        if (startIdx == 0) return null;
        for (int i = startIdx; i <= endIdx; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < cellKey.length; j++) {
                Cell cell = row.getCell(j);
                if (j == 3) {
                    Date date = HSSFDateUtil.getJavaDate(row.getCell(3).getNumericCellValue());
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    valueMap.put(cellKey[j], sdf.format(date));
                } else if (j == 16) {
                    valueMap.put(cellKey[j], (int) cell.getNumericCellValue() + "");
                } else if (j == 17) {
                    valueMap.put(cellKey[j], cell.getNumericCellValue() + "");
                } else {
                    cell.setCellType(CellType.STRING);
                    valueMap.put(cellKey[j], cell.getStringCellValue());
                }
            }
            T t = c.newInstance();
            //使用BeanUtils将封装的属性注入对象
            BeanUtils.populate(t, valueMap);
            //将对象添加至容器
            beanList.add(t);
        }


        List<PoiProblemDto> poiProblemDto = (List<PoiProblemDto>) beanList;

        List<PoiProblemDto> finalList = new ArrayList<>();
        for (PoiProblemDto dto : poiProblemDto) {
            PoiProblemDto poiProblem = poiProblemDao.getPoiProblem(dto);
            Integer taskId = poiProblem.getTaskId();
            String taskName = poiProblem.getTaskName();


            String[] str = new String[6];
            str[0] = poiProblem.getCompanyCode();
            str[1] = poiProblem.getCheckItemCode();
            str[2] = poiProblem.getTaskAndProcessCode();
            str[3] = poiProblem.getProblemDescriptionCode();
            str[4] = poiProblem.getProblemStation();
            str[5] = poiProblem.getResponseEmpGroup();


            int flag = 1;
            for (String tmp : str) {
                if (StringUtils.isBlank(tmp)) {
                    flag = -1;
                    break;
                }
            }
            Integer responsePersonID = poiProblem.getResponsePersonID();
            if (responsePersonID == null || flag == -1) break;

            taskId = taskId == null ? -1 : taskId;
            taskName = StringUtils.isNotBlank(taskName) ? taskName : "计划外";

            dto.setManyAttribute(taskId,taskName,str[0],str[1],str[2],str[3],str[4],responsePersonID,str[5]);

//            dto.setTaskId(taskId);
//            dto.setTaskName(taskName);
//            dto.setCompanyCode(str[0]);
//            dto.setCheckItemCode(str[1]);
//            dto.setTaskAndProcessCode(str[2]);
//            dto.setProblemDescriptionCode(str[3]);
//            dto.setProblemStation(str[4]);
//            dto.setResponsePersonID(responsePersonID);
//            dto.setResponseEmpGroup(str[5]);

            String hash = HashUtil.hash(dto.getCompanyName() + dto.getCheckDate() + dto.getDescription() + str[1]);

            if (problemImportDao.getHashCount(hash) == 0) {
                dto.setHash(hash);
                finalList.add(dto);
            }
        }
        if (finalList.size() > 0) {
            poiProblemDao.addProblemList(finalList);
        }


        return NR.getPoiProblemReturn(CodeDict.SUCCESS, 0);
    }


    public <T> String getCheckListItemsBeanList(Workbook wb, String[] cellKey,String checkListID) throws Exception {
        //创建实体类对象容器
        Date date=new Date();
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmm");
        String timeCode=df.format(date);
        List<CheckListItemsDto> beanList = new ArrayList<>();
        HashMap<String, String> checkListItemValueMap = new HashMap<>();
        Sheet sheet = wb.getSheetAt(0);
        String parentItemName="";
        Integer parentCount=-1;//父节点个数，用于父节点编码
        Integer sonCount=1;//子节点个数，用于子节点编码
        DataFormatter dataFormat=new DataFormatter();
        String parentCode="";
        //遍历excel文件
        for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
            Row row = sheet.getRow(j);
            int count =0;
            //判断该父节点是否第一次出现
            Cell celltest=row.getCell(5);
            String str;
            if(celltest==null){
                str=null;
            }else{
                str=dataFormat.formatCellValue(celltest).toString();
            }
            if(str==null||parentItemName.equals(str)||"".equals(str)){//父节点不是第一次出现
                sonCount++;
            }else{//父节点第一次出现
                parentItemName=str;
                parentCount++;
                sonCount=1;
                if(parentCount<10){//生成编码
                    parentCode="0"+parentCount;
                }else{
                    parentCode=""+parentCount;
                }
                //生成父节点对象
                CheckListItemsDto parent=new CheckListItemsDto();
                parent.setCheckListItemName(parentItemName);
                parent.setCheckListItemCode(parentCode);
                parent.setCheckListID(checkListID);
                parent.setInsertDateCode(timeCode);
                Cell cell = row.getCell(6);
                String cellValue;
                cellValue = dataFormat.formatCellValue(cell).toString();
                parent.setCheckItemNumber(cellValue);//父节点序号和第一个子节点序号相同
                parent.setStatus("启用");//状态都为启用
                beanList.add(parent);
            }
            for (int i = 0; i < cellKey.length; i++) {//判断是否为空行
                Cell cell = row.getCell(i+6);
                String a=dataFormat.formatCellValue(cell).toString();
                if (cell == null || a.length() == 0) {
                    count = count + 1;
                }

            }
            if(count == cellKey.length){//空行
                continue;
            }else {
                for(int i=0;i<cellKey.length;i++){//封装子节点
                    Cell cell = row.getCell(i+6);
                    String cellValue;
                    cellValue = dataFormat.formatCellValue(cell).toString();
                    if(i<2){//对前6个字段进行校验
                        if(cellValue==null||"".equals(cellValue)){//j行存在空值
                            return NR.getPoiProblemReturn(CodeDict.POI_PROBLEM_EMPTY_ROW,j+1);
                        }
                    }
                    checkListItemValueMap.put(cellKey[i], cellValue);
                }
                CheckListItemsDto sonItem=new CheckListItemsDto();
                //使用BeanUtils将封装的属性注入对象
                BeanUtils.populate(sonItem, checkListItemValueMap);
                if("".equals(sonItem.getScore())||sonItem.getScore()==null){//未填分数默认为0
                    sonItem.setScore("0");
                }
                sonItem.setStatus("启用"); //状态都为启用
                sonItem.setCheckListID(checkListID);
                String sonCode="";
                if(sonCount<10){
                    sonCode=parentCode+"0"+sonCount;
                }else{
                    sonCode=parentCode+sonCount;
                }
                sonItem.setCheckListItemCode(sonCode);
                sonItem.setInsertDateCode(timeCode);
                beanList.add(sonItem);
            }
        }
        if (beanList.size() > 0) {
            checkListItemDao.addCheckListItem(beanList);
            return NR.getPoiProblemReturn(CodeDict.SUCCESS,-1);
        } else {
            return NR.getPoiProblemReturn(CodeDict.POI_PROBLEM_EMPTY_FIRST,-1);
        }
    }

    @Transactional
    public <T> String getEmployeeBeanList(Workbook wb, String[] cellKey) throws Exception {
        //创建实体类对象容器
        List<EmployeePojo> beanList = new ArrayList<>();
        HashMap<String, String> valueMap = new HashMap<>();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter dataFormat=new DataFormatter();
        //遍历excel文件
        for (int j = 2; j < sheet.getPhysicalNumberOfRows(); j++) {
            Row row = sheet.getRow(j);
            //获取name
            Cell name=row.getCell(1);
            String nameStr=dataFormat.formatCellValue(name).toString();
            //如果姓名为空则不加入到数据库中
            if(nameStr==null || "".equals(nameStr)){
                continue;
            }

            valueMap.put("name", nameStr);
            //通过CompanyName获取CompanyCode

            //公司从最上级开始找，直接找到最后
            String preCompanyCode = "";
            String companyCode = "";

            for(int i=2;i<=5;i++){
                Cell companyName=row.getCell(i);
                String companyNameStr=dataFormat.formatCellValue(companyName).toString();
                preCompanyCode = companyCode;
                if(companyNameStr==null || "".equals(companyNameStr)){
                    //如果该级公司为空，则直接赋值上一级的公司preCompanyCode
                    valueMap.put("companyCode", preCompanyCode);
                    break;
                }else {//如果当前列的公司不为空，则获得其companyCode
                    List<String> companyCodes=companyDao.queryCompanyCodes(companyNameStr);
                    //如果companyCodes大于1，则说明子公司有重名的情况，则根据preCompanyCode判断
                    if(companyCodes.size()>1){
                        for(String code:companyCodes){
                            //如果与preCompanyCode相等则继续往下走
                            if(code.substring(0,preCompanyCode.length()).equals(preCompanyCode)){
                                companyCode=code;
                                break;
                            }
                        }
                    }else if(companyCodes.size()==0){
                        return NR.r(CodeDict.UPDATE_FAILE,"序号为"+(j-1)+"行的第"+(i+1)+"列，"+companyNameStr+"的公司名及companyCode不存在，请在公司表(sys_company)中添加后重试");
                    }else {
                        companyCode=companyCodes.get(0);
                    }
                }
                if(i==5){
                    valueMap.put("companyCode", companyCode);
                    break;
                }
            }

//            Cell companyName=row.getCell(2);
//            String companyNameStr=dataFormat.formatCellValue(companyName).toString();
//            String companyCode=companyDao.queryByCompanyName(companyNameStr);//一级单位
//            for(int i=3;i<=5;i++){//从母公司逐级向下找到子公司
//                if(companyCode==null||"".equals(companyCode)){//对于，没有查询到的Code,直接赋""
//                    valueMap.put("companyCode", "");
//                    break;
//                }
//                companyName=row.getCell(i);
//                companyNameStr=dataFormat.formatCellValue(companyName).toString();
//                if ("/".equals(companyNameStr)){
//                    valueMap.put("companyCode", companyCode);
//                    break;
//                }else if(i==5){
//                    companyCode=companyDao.likeQueryByCompanyName(companyNameStr,companyCode);
//                    valueMap.put("companyCode", companyCode);
//                }else {
//                    companyCode=companyDao.likeQueryByCompanyName(companyNameStr,companyCode);
//                    continue;
//                }
//            }
            //获取其他属性
            for (int i = 6; i < 12; i++) {
                Cell cell = row.getCell(i);
//                if (cell == null && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
//                    return NR.getEmptyNote(row.getPhysicalNumberOfCells(), i);
//                }
                String cellValue;
                cellValue=dataFormat.formatCellValue(cell).toString();
                //对日期数据特殊处理
                if(cellValue!=null&&!"".equals(cellValue)){//对不为空的日期进行处理
                    if(i==9){
                        cellValue=cellValue.replace("/","-");
                        if(cellValue.split("-").length<3){//没有日期的默认1号
                            cellValue=cellValue+"-01";
                        }
                    }
                    if(i==10){
                        cellValue=cellValue.replace("/","-");
                        if(cellValue.split("-").length<3){//没有日期的默认1号
                            cellValue=cellValue+"-01";
                        }
                    }
                    if(i==11){
                        cellValue=cellValue.replace("/","-");
                        if(cellValue.split("-").length<3){//没有日期的默认1号
                            cellValue=cellValue+"-01";
                        }
                    }
                }
                if ("".equals(cellValue)){
                    cellValue=null;
                }
                valueMap.put(cellKey[i-4], cellValue);
            }
            EmployeePojo employeePojo=new EmployeePojo();
            //使用BeanUtils将封装的属性注入对象
            BeanUtils.populate(employeePojo, valueMap);

            //首先判断beanList中是否存在用户重复，再判断该用户是否已在数据库中存在
            int flag=0;
            for(EmployeePojo pojo: beanList){
                if(pojo.getName().equals(employeePojo.getName())&&pojo.getCompanyCode().equals(employeePojo.getCompanyCode())){
                    flag=1;
                    break;
                }
            }
            int employeeCount = employeeManagementDao.queryEmployeeByCodeAndName(employeePojo.getCompanyCode(),employeePojo.getName());

            //flag为0才加入到beanList中
            if(flag==0){
                if(employeeCount==0){
                    //将对象添加至容器
                    beanList.add(employeePojo);
                }else {
                    return NR.r(CodeDict.UPDATE_FAILE,"序号为"+(j-1)+"行，"+employeePojo.getName()+"在该公司下已存在同名员工，请修改后重试");
                }
            }




        }
        if (beanList.size() > 0) {
            //删除redis中关于员工的缓存,避免出现脏数据
            deleteCacheUtil.deleteEmployeesCache();
            employeeManagementDao.addEmployeePojos(beanList);
            return NR.getPoiProblemReturn(CodeDict.SUCCESS, 0);
        } else {
            return NR.getPoiProblemReturn(CodeDict.POI_PROBLEM_UNKNOWN_ERROR, 0);
        }
    }


}
